{$A8,B-,C+,D+,E-,F-,G+,H+,I+,J-,K-,L+,M-,N-,O+,P+,Q-,R-,S-,T-,U-,V+,W-,X+,Y+,Z1}
{$MINSTACKSIZE $00004000}
{$MAXSTACKSIZE $00100000}
{$IMAGEBASE $00400000}
{$APPTYPE GUI}
{$WARN SYMBOL_DEPRECATED ON}
{$WARN SYMBOL_LIBRARY ON}
{$WARN SYMBOL_PLATFORM ON}
{$WARN SYMBOL_EXPERIMENTAL ON}
{$WARN UNIT_LIBRARY ON}
{$WARN UNIT_PLATFORM ON}
{$WARN UNIT_DEPRECATED ON}
{$WARN UNIT_EXPERIMENTAL ON}
{$WARN HRESULT_COMPAT ON}
{$WARN HIDING_MEMBER ON}
{$WARN HIDDEN_VIRTUAL ON}
{$WARN GARBAGE ON}
{$WARN BOUNDS_ERROR ON}
{$WARN ZERO_NIL_COMPAT ON}
{$WARN STRING_CONST_TRUNCED ON}
{$WARN FOR_LOOP_VAR_VARPAR ON}
{$WARN TYPED_CONST_VARPAR ON}
{$WARN ASG_TO_TYPED_CONST ON}
{$WARN CASE_LABEL_RANGE ON}
{$WARN FOR_VARIABLE ON}
{$WARN CONSTRUCTING_ABSTRACT ON}
{$WARN COMPARISON_FALSE ON}
{$WARN COMPARISON_TRUE ON}
{$WARN COMPARING_SIGNED_UNSIGNED ON}
{$WARN COMBINING_SIGNED_UNSIGNED ON}
{$WARN UNSUPPORTED_CONSTRUCT ON}
{$WARN FILE_OPEN ON}
{$WARN FILE_OPEN_UNITSRC ON}
{$WARN BAD_GLOBAL_SYMBOL ON}
{$WARN DUPLICATE_CTOR_DTOR ON}
{$WARN INVALID_DIRECTIVE ON}
{$WARN PACKAGE_NO_LINK ON}
{$WARN PACKAGED_THREADVAR ON}
{$WARN IMPLICIT_IMPORT ON}
{$WARN HPPEMIT_IGNORED ON}
{$WARN NO_RETVAL ON}
{$WARN USE_BEFORE_DEF ON}
{$WARN FOR_LOOP_VAR_UNDEF ON}
{$WARN UNIT_NAME_MISMATCH ON}
{$WARN NO_CFG_FILE_FOUND ON}
{$WARN IMPLICIT_VARIANTS ON}
{$WARN UNICODE_TO_LOCALE ON}
{$WARN LOCALE_TO_UNICODE ON}
{$WARN IMAGEBASE_MULTIPLE ON}
{$WARN SUSPICIOUS_TYPECAST ON}
{$WARN PRIVATE_PROPACCESSOR ON}
{$WARN UNSAFE_TYPE OFF}
{$WARN UNSAFE_CODE OFF}
{$WARN UNSAFE_CAST OFF}
{$WARN OPTION_TRUNCATED ON}
{$WARN WIDECHAR_REDUCED ON}
{$WARN DUPLICATES_IGNORED ON}
{$WARN UNIT_INIT_SEQ ON}
{$WARN LOCAL_PINVOKE ON}
{$WARN MESSAGE_DIRECTIVE ON}
{$WARN TYPEINFO_IMPLICITLY_ADDED ON}
{$WARN RLINK_WARNING ON}
{$WARN IMPLICIT_STRING_CAST ON}
{$WARN IMPLICIT_STRING_CAST_LOSS ON}
{$WARN EXPLICIT_STRING_CAST OFF}
{$WARN EXPLICIT_STRING_CAST_LOSS OFF}
{$WARN CVT_WCHAR_TO_ACHAR ON}
{$WARN CVT_NARROWING_STRING_LOST ON}
{$WARN CVT_ACHAR_TO_WCHAR ON}
{$WARN CVT_WIDENING_STRING_LOST ON}
{$WARN NON_PORTABLE_TYPECAST ON}
{$WARN XML_WHITESPACE_NOT_ALLOWED ON}
{$WARN XML_UNKNOWN_ENTITY ON}
{$WARN XML_INVALID_NAME_START ON}
{$WARN XML_INVALID_NAME ON}
{$WARN XML_EXPECTED_CHARACTER ON}
{$WARN XML_CREF_NO_RESOLVE ON}
{$WARN XML_NO_PARM ON}
{$WARN XML_NO_MATCHING_PARM ON}
{$WARN IMMUTABLE_STRINGS OFF}
unit ServerMethodsUnit1;

interface

uses System.SysUtils, System.Classes, Datasnap.DSServer, Datasnap.DSAuth,
  Data.FMTBcd, Data.DBXMSSQL, Data.DB, Data.SqlExpr, Datasnap.Provider,
  Datasnap.DBClient, Data.DBXJSON, Data.DBXJSONCommon, Data.DBXCommon,
  IdTCPConnection;

type
  TServerMethods1 = class(TDSServerModule)
    ClientDataSet1: TClientDataSet;
    DataSetProvider1: TDataSetProvider;
    AlexConn: TSQLConnection;
    SQLDataSet1: TSQLDataSet;
    procedure DSServerModuleCreate(Sender: TObject);
  private
    { Private declarations }
    sql: string;

    function GetConnection: TSQLConnection;
    function OpenDataSet(sql: string): string;
    function ExecuteSQL(sql: string): boolean;
    function ExecuteScalar(sql: string): integer;
    function GetNewOrderNo: string;
    function DeleteAnOrder(OrderNo: string): boolean;

  public
    { Public declarations }
    function GetMenuItems: string;
    function GetCategoryItems: string;
    function GetTablePages: string;
    function GetTableLayout: string;
    function GetProfile: string;
    function GetMenuInstructionLink: string;
    function GetVIPTable: string;
    function SaveOrder(OrderNo, TableNo, sql: string): boolean;
    function SaveLockName(cliTableNo: string): boolean;
    function GetOrderByTableNo(TableNo: string): string;
    function GetOrderItemByOrderNo(OrderNo: string): string;

  end;

implementation

{$R *.dfm}

uses System.StrUtils, ServerContainerUnit1;

function TServerMethods1.GetCategoryItems: string;
begin
  sql := 'select * from category order by code';
  result := OpenDataSet(sql);
end;

function TServerMethods1.GetMenuInstructionLink: string;
begin
  sql := 'select ml.*,mi.Description1 from SubMenuLinkDetail ml join Menuitem '
    + 'mi on ml.SubMenuCode = mi.ItemCode';
  result := OpenDataSet(sql);
end;

function TServerMethods1.GetMenuItems: string;
begin
  sql := 'select itemcode,category,description1,' +
    'price,multiple,subdescription,price1,subdescription1,price2,' +
    'subdescription2,price3,subdescription3,price,HappyHourPrice1,HappyHourPrice2,'
    + 'HappyHourPrice3,HappyHourPrice4,WeekendPrice,WeekendPrice1,' +
    'WeekendPrice2,WeekendPrice3,Instruction,TaxRate ' +
    ' from menuitem where Active = 1 and Instruction <> 1 order by itemcode';
  result := OpenDataSet(sql);
end;

function TServerMethods1.GetProfile: string;
begin
  // SQLDataSet1.SQLConnection := GetConnection;
  sql := 'select ForceVIPDiscount,NotAllowModify,RoundingFlag,ServiceChargeRate,'
    + 'HappyHour,HappyHourStartTime,HappyHourEndTime,EnableWeekendPriceFunction,'
    + 'WeekendPriceStartDay,WeekendPriceEndDay from profile';
  result := OpenDataSet(sql);
end;

function TServerMethods1.GetTablePages: string;
begin
  // SQLDataSet1.SQLConnection := GetConnection;
  sql := 'select * from TablePage';
  result := OpenDataSet(sql);
end;

function TServerMethods1.GetVIPTable: string;
begin
  sql := 'select * from VIPTable';
  result := OpenDataSet(sql);
end;

function TServerMethods1.GetTableLayout: string;
begin
  // SQLDataSet1.SQLConnection := GetConnection;
 // sql := 'select TableSet.TableNo, Description, ButtonShape, ButtonWidth, ButtonHeight, ButtonX, ButtonY,'
 //   + 'propertyFlag,pageFlag,Status,TableLockerName FROM TableSet';
 sql := 'SELECT TableSet.TableNo, Description, ButtonShape, ButtonWidth, ButtonHeight, ButtonX, ButtonY,'
       + 'PropertyFlag, FontName, FontSize, FontBold, FontItalic, FontUnderline, FontStrikeout, PageFlag, OrderNo,OrderDate,tableLockername'
       + ',OrderTable.OrderPrinted '
       + 'FROM TableSet Left Join'
       + '(Select TableNo, OrderNo,OrderDate,OrderPrinted From OrderH'
       + ' Where Credit=0 and (PriceIncludesGST <> 0) and'
       + ' (Amount + ServiceCharge + Tips + Surcharge + OtherCharge - PaidAmount>=0)'
       + ' and '
       + 'OrderDate>= convert(char(10),GetDate(),120) + '' '' + (SELECT TOP 1 convert(char(8),BeginTime,108) from Profile)'
       + ' Union All '
       + 'Select TableNo, OrderNo,OrderDate,OrderPrinted '
       + 'From OrderH Where Credit=0 and (PriceIncludesGST = 0) and '
       + '(Amount + ServiceCharge + Tips + Surcharge + OtherCharge + GST - PaidAmount>=0)'
       + ' and '
       + 'OrderDate>=convert(char(10),GetDate(),120) + '' '' + (SELECT TOP 1 convert(char(8),BeginTime,108) from Profile)) As OrderTable'
       + ' On TableSet.TableNo=OrderTable.TableNo';
  result := OpenDataSet(sql);
end;

function TServerMethods1.GetOrderByTableNo(TableNo: string): string;
begin
  sql := 'SELECT TableSet.TableNo,OrderH.BookingNo,OrderH.OrderNo,OrderH.OrderDate,' +
    'OrderH.OrderDate,OrderH.Persons,OrderH.Amount,' +
    'OrderH.DollarDiscount,OrderH.DiscountKind,OrderH.GST ' +
    'FROM TableSet Left Join ' +
    '(Select TableNo,BookingNo,OrderNo,OrderDate,Persons,Amount,DollarDiscount,DiscountKind,GST From OrderH ' +
    ' Where Credit=0 and (PriceIncludesGST <> 0) and ' +
    ' (Amount + ServiceCharge + Tips + Surcharge + OtherCharge - PaidAmount>=0) and ' +
    'OrderDate>= convert(char(10),GetDate(),120) + '' '' + (SELECT TOP 1 convert(char(8),BeginTime,108) ' +
    ' from Profile) Union All ' +
    'Select TableNo,BookingNo, OrderNo,OrderDate,Persons,Amount,DollarDiscount,DiscountKind,GST ' +
    'From OrderH Where Credit=0 and (PriceIncludesGST = 0) and ' +
    '(Amount + ServiceCharge + Tips + Surcharge + OtherCharge + GST - PaidAmount>=0) and ' +
    'OrderDate>=convert(char(10),GetDate(),120) + '' '' + (SELECT TOP 1 convert(char(8),BeginTime,108) ' +
    'from Profile)) As OrderH ' + 'On TableSet.TableNo=OrderH.TableNo ' +
    'WHERE TableSet.TableNo = ''' + TableNo + '''';
  result := OpenDataSet(sql);
end;

function TServerMethods1.GetOrderItemByOrderNo(OrderNo: string): string;
begin
  sql := 'SELECT * from OrderI where OrderNo =''' + OrderNo + '''';


  sql := 'select a.*,case b.instruction when 0 then b.description1 ' +
          'else ''[*] '' + b.description1 end as description '+
          'from OrderI a join menuitem b on a.itemcode = b.itemcode where orderno = '''+ OrderNo + '''';

  result := OpenDataSet(sql);
end;

function TServerMethods1.OpenDataSet(sql: string): string;
begin
  SQLDataSet1.Active := false;
  ClientDataSet1.Active := false;
  ClientDataSet1.CommandText := sql;
  // ClientDataSet1.CommandType := ctQuery;
  ClientDataSet1.Active := true;
  result := ClientDataSet1.XMLData;
end;

procedure TServerMethods1.DSServerModuleCreate(Sender: TObject);
begin
  SQLDataSet1.SQLConnection := GetConnection;
end;

function TServerMethods1.GetNewOrderNo: string;
begin
  sql := 'SELECT ISNULL(CAST(MAX(OrderNo) AS int) + 1,substring(CONVERT(varchar(12) , getdate(), 112 ),3,6) + ''0001'')'
    + ' FROM OrderH WHERE OrderNo LIKE substring(CONVERT(varchar(12) , getdate(), 112 ),3,6) + ''%''';
  ClientDataSet1.Active := false;
  ClientDataSet1.CommandText := sql;
  ClientDataSet1.Active := true;
  result := ClientDataSet1.Fields[0].asstring;
end;

function TServerMethods1.DeleteAnOrder(OrderNo: string): boolean;
var
  s: string;
begin
  s := 'delete from orderH where OrderNo = ''' + OrderNo + '''; delete from OrderI ' +
    'where OrderNo = ''' + OrderNo + '''';
  result := ExecuteSQL(s);
end;

function TServerMethods1.SaveLockName(cliTableNo: string): boolean;
var
  haslocked: boolean;
begin
//  sql := 'update TABLESET SET tableLockerName =null where TableLockerName = ''' +
//    ServerContainer1.testIP + '''';
//  ExecuteSQL(sql);
  sql := 'select count(1) from tableset where tableno = ''' +
    cliTableNo + ''' and not(tablelockername is null) and tablelockername <> ''' +
    ServerContainer1.testIP + '''';    // test the tableno if locked by another IP
  haslocked := ExecuteScalar(sql) > 0 ;
  if not haslocked then
  begin
    sql := 'UPDATE TABLESET SET tableLockerName = ''' + ServerContainer1.testIP
      + ''' where tableno = ''' + cliTableNo + '''';
    ExecuteSQL(sql);
  end;
  result := not haslocked;
end;

function TServerMethods1.SaveOrder(OrderNo, TableNo, sql: string): boolean;
var
  sqlunlock: string;
begin
  if OrderNo = '' then
  begin
    OrderNo := GetNewOrderNo;
    // ExecuteSQL('Update TableSet set Status = 1 where TableNo =''' + TableNo +'''');
    sql := ReplaceStr(sql, '@OrderNo', OrderNo);
  end
  else
    DeleteAnOrder(OrderNo);
  // unlock the
  sqlunlock :=
    'Update tableset set tablelockername = null where tableno = ''' +
    TableNo + '''';
  ExecuteSQL(sqlunlock);
  // Insert OrderH and OrderI
  result := ExecuteSQL(sql);
end;

function TServerMethods1.ExecuteScalar(sql: string): integer;
var
  i: integer;
begin
  SQLDataSet1.Active := false;
  SQLDataSet1.CommandText := sql;
  SQLDataSet1.CommandType := ctQuery;
  SQLDataSet1.Active := true;
  i := SQLDataSet1.Fields[0].asinteger;
  result := i;
end;

function TServerMethods1.ExecuteSQL(sql: string): boolean;
var
  i: integer;
begin
  SQLDataSet1.Active := false;
  SQLDataSet1.CommandText := sql;
  SQLDataSet1.CommandType := ctQuery;
  //SQLDataSet1.Active := true;
  i := SQLDataSet1.ExecSQL;

  result := i > 0;
end;

function TServerMethods1.GetConnection: TSQLConnection;
begin
  AlexConn.DriverName := 'MSSQL';
  // AlexConn.Params.Clear;
  AlexConn.Params.Text := 'SchemaOverride=%.dbo' + #$D#$A +
    'DriverUnit=Data.DBXMSSQL' + #$D#$A +
    'DriverPackageLoader=TDBXDynalinkDriverLoader,DBXCommonDriver180.bpl' +
    #$D#$A + 'DriverAssemblyLoader=Borland.Data.TDBXDynalinkDriverLoader,Borland.Data.DbxCommonDriver,Version=18.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b'
    + #$D#$A +
    'MetaDataPackageLoader=TDBXMsSqlMetaDataCommandFactory,DbxMSSQLDriver180.bpl'
    + #$D#$A +
    'MetaDataAssemblyLoader=Borland.Data.TDBXMsSqlMetaDataCommandFactory,Borland.Data.DbxMSSQLDriver,Version=18.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b'
    + #$D#$A + 'GetDriverFunc=getSQLDriverMSSQL' + #$D#$A +
    'LibraryName=dbxmss.dll' + #$D#$A + 'VendorLib=sqlncli.dll' + #$D#$A +
    'VendorLibWin64=sqlncli.dll' + #$D#$A + 'HostName=alex-hp\sqlexpress' + #$D#$A +
    'DataBase=Albury' + #$D#$A + 'MaxBlobSize=-1' + #$D#$A + 'LocaleCode=0000' +
    #$D#$A + 'IsolationLevel=ReadCommitted' + #$D#$A + 'OSAuthentication=False'
    + #$D#$A + 'PrepareSQL=True' + #$D#$A + 'User_Name=alex' + #$D#$A +
    'Password=55213430' + #$D#$A + 'BlobSize=-1' + #$D#$A;
  result := AlexConn;
end;

end.
